package T02_主要API的使用;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.*;
import org.junit.jupiter.api.Test;
import popj.Customers;
import T03_utils.JDBCUtils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 *测试查询
 */
public class T02_QueryTest {

    /**
     * 查询一条
     * BeanHandler:是ResultSetHandler接口的实现类,用于封装表中的一条记录
     */
    @Test
    public  void  testQuery01(){
        Connection conn=null;
        try {
            QueryRunner queryRunner=new QueryRunner();
            conn= JDBCUtils.getConnection();
            String sql="select id,name,email,birth from customers where id=?";
            BeanHandler<Customers> handler=new BeanHandler<>(Customers.class);
            Customers query = queryRunner.query(conn, sql, handler, 1);
            System.out.println(query);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(conn,null);
        }
    }

    /**
     * 查询多条
     * BeanListHandler:是ResultSetHandler接口的实现类,用于封装表中的多条记录构成的集合
     */
    @Test
    public  void  testQuery02(){
        Connection conn=null;
        try {
            QueryRunner queryRunner=new QueryRunner();
            conn= JDBCUtils.getConnection();
            String sql="select id,name,email,birth from customers";
            BeanListHandler<Customers> handler=new BeanListHandler<>(Customers.class);
            List<Customers> query = queryRunner.query(conn, sql, handler);
            query.forEach(System.out::println);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(conn,null);
        }
    }

    /**
     * MapHandler:是ResultSetHandler接口的实现类,对应表中的一条记录
     * 将字段及相应字段的值作为map中的key和value
     */
    @Test
    public  void  testQuery03(){
        Connection conn=null;
        try {
            QueryRunner queryRunner=new QueryRunner();
            conn= JDBCUtils.getConnection();
            String sql="select id,name,email,birth from customers where id=?";
            MapHandler mapHandler=new MapHandler();
            Map<String, Object> mapQuery = queryRunner.query(conn, sql, mapHandler, 1);
            System.out.println(mapQuery);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(conn,null);
        }
    }

    /**
     * MapListHandler:是ResultSetHandler接口的实现类,对应表中的多条记录
     * 将字段及相应字段的值作为map中的key和value,将这些map添加到List中
     */
    @Test
    public  void  testQuery04(){
        Connection conn=null;
        try {
            QueryRunner queryRunner=new QueryRunner();
            conn= JDBCUtils.getConnection();
            String sql="select id,name,email,birth from customers";
            MapListHandler mapListHandler=new MapListHandler();
            List<Map<String, Object>> query = queryRunner.query(conn, sql, mapListHandler);
            query.forEach(System.out::println);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(conn,null);
        }
    }

    /**
     * ScalarHandler:用于查询特殊值
     */
    @Test
    public  void  testQuery05(){
        Connection conn=null;
        try {
            QueryRunner queryRunner=new QueryRunner();
            conn= JDBCUtils.getConnection();
            String sql="select count(*) from customers";
            ScalarHandler scalarHandler=new ScalarHandler();
            Long query = (Long) queryRunner.query(conn, sql, scalarHandler);
            System.out.println(query);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(conn,null);
        }
    }
    @Test
    public  void  testQuery06(){
        Connection conn=null;
        try {
            QueryRunner queryRunner=new QueryRunner();
            conn= JDBCUtils.getConnection();
            String sql="select max(birth) from customers";
            ScalarHandler scalarHandler=new ScalarHandler();
            Date query = (Date) queryRunner.query(conn, sql, scalarHandler);
            System.out.println(query);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(conn,null);
        }
    }

    @Test
    public  void  testQuery07(){
        Connection conn=null;
        try {
            QueryRunner queryRunner=new QueryRunner();
            conn= JDBCUtils.getConnection();
            String sql="select id,name,email,birth from customers where id=?";
            //注意泛型写前边不是能省略后边a 为什么现在不行  因为这个是一个匿名实现类
            ResultSetHandler<Customers> handler=new ResultSetHandler<Customers>() {
                @Override
                public Customers handle(ResultSet rs) throws SQLException {
                    //return new Customers(12,"杨幂","Jack@126.com",new Date(234324234324L));
                    if(rs.next()){
                        int id=rs.getInt("id");
                        String name=rs.getString("name");
                        String email=rs.getString("email");
                        Date birth=rs.getDate("birth");
                        Customers customers=new Customers(id,name,email,birth);
                        return customers;
                    }
                    return null;
                }
            };
            Customers customers = queryRunner.query(conn, sql,handler,1 );
            System.out.println(customers);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(conn,null);
        }
    }

    /**
     * 格林威治时间格式化为 正常日期类型
     * @throws ParseException
     */
    @Test
    public  void test08() throws ParseException {
        SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd");
        String date=sdf.format(new Date());//格式化日期
        Date parse = sdf.parse(date);//将字符串格式的日期转换成Date类型
        java.sql.Date date1=new java.sql.Date(parse.getTime());
        Customers customers=new Customers(12,"茂昌","maochang@qq.com",date1);
        System.out.println(customers);
    }

    @Test
    public  void  test09(){
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");//格式化规则
        Date date =new Date();//获得要处理的时间 Date型
        String strDate= sdf.format(date);//格式化成yyyy-MM-dd格式的时间字符串
        Date newDate = null;//将字符串转换为时间格式
        try {
            newDate = sdf.parse(strDate);
        } catch (ParseException e) {
            e.printStackTrace();
        }

        java.sql.Date resultDate = new java.sql.Date(newDate.getTime());//最后转换成 java.sql.Date类型数据就可以了，此处最开始数据类型为Date。
        System.out.println(resultDate);
    }
}
